USE [Daco]
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetMenuTable') AND type in (N'P')))
	DROP PROCEDURE dbo.GetMenuTable
GO
CREATE PROCEDURE dbo.GetMenuTable
AS
BEGIN
	SELECT     MenuID, ISNULL(ParentMenuID, - 1) AS ParentMenuID, Name, Title, ISNULL(ImageUrl, N'') AS ImageUrl, RelativeUrl, Priority
	FROM       Menus
	ORDER BY	ParentMenuID ASC, Priority ASC
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetCurrentWebSettings') AND type in (N'P')))
	DROP PROCEDURE dbo.GetCurrentWebSettings
GO
CREATE PROCEDURE dbo.GetCurrentWebSettings
AS
BEGIN
	SELECT     WebSettingID, SiteUrl, SiteName, SiteDescription, Address
	FROM         WebSettings
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.UpdateCurrentWebSetting') AND type in (N'P')))
	DROP PROCEDURE dbo.UpdateCurrentWebSetting
GO
CREATE PROCEDURE dbo.UpdateCurrentWebSetting
	@Logo		NVARCHAR(256),
	@Slogan		NVARCHAR(256),
	@SiteUrl	NVARCHAR(256),
	@SiteName	NVARCHAR(256),
	@SiteDescription	NVARCHAR(500),
	@Address	NVARCHAR(500)
AS
BEGIN
	UPDATE WebSettings SET
		Logo = @Logo,
		Slogan = @Slogan,
		SiteUrl = @SiteUrl,
		SiteName = @SiteName,
		SiteDescription = @SiteDescription,
		Address = @Address
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetFileAttachmentTable') AND type in (N'P')))
	DROP PROCEDURE dbo.GetFileAttachmentTable
GO
CREATE PROCEDURE dbo.GetFileAttachmentTable
	@ProductCategoryIDList	VARCHAR(500),
	@PageIndex				INT,
	@PageSize				INT
AS
BEGIN
	DECLARE @PageIndexForFileAttachment TABLE
	(
		IndexID		INT IDENTITY(0,1),
		AttachmentID	VARCHAR(8)
	)

	DECLARE @PageLowerBound	INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound INT
		SET @PageUpperBound = @PageLowerBound + @PageSize - 1
		
	INSERT INTO @PageIndexForFileAttachment (AttachmentID)
		SELECT AttachmentID FROM Attachments WHERE CategoryID IN 
			(SELECT ItemId FROM dbo.Split(@ProductCategoryIDList, ','))
			
	SELECT p.AttachmentID, a.Title, a.[FileName], a.FileExtension, a.ContentType
	FROM	 @PageIndexForFileAttachment p INNER JOIN
				Attachments a ON p.AttachmentID = a.AttachmentID
	WHERE	p.IndexID BETWEEN @PageLowerBound AND @PageUpperBound
	
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetFileAttachment') AND type in (N'P')))
	DROP PROCEDURE dbo.GetFileAttachment
GO
CREATE PROCEDURE dbo.GetFileAttachment
	@AttachmentID	VARCHAR(8)
AS
BEGIN
	SELECT * FROM Attachments WHERE AttachmentID = @AttachmentID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyProfiles_GetProfileList') AND type in (N'P')))
	DROP PROCEDURE dbo.CompanyProfiles_GetProfileList
GO
CREATE PROCEDURE dbo.CompanyProfiles_GetProfileList
AS
BEGIN
	SET NOCOUNT ON
	SELECT ID, Name FROM CompanyProfiles ORDER BY [Primary] DESC
	SET NOCOUNT OFF
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyProfiles_AddProfile') AND type in (N'P')))
	DROP PROCEDURE dbo.CompanyProfiles_AddProfile
GO
CREATE PROCEDURE dbo.CompanyProfiles_AddProfile
	@ID		VARCHAR(50),
	@Name	NVARCHAR(256),
	@Body	NVARCHAR(MAX),
	@Primary	BIT
AS
BEGIN
	IF(@Primary = 1)
		UPDATE CompanyProfiles SET [Primary] = 0
	
	IF(EXISTS(SELECT * FROM CompanyProfiles WHERE ID = @ID))
		UPDATE CompanyProfiles SET Name = @Name, [Body] = @Body, [Primary] = @Primary WHERE ID = @ID
	ELSE
		INSERT INTO CompanyProfiles(ID, Name, [Body], [Primary])
		VALUES (@ID, @Name, @Body, @Primary)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyProfiles_AddProfile') AND type in (N'P')))
	DROP PROCEDURE dbo.CompanyProfiles_AddProfile
GO
CREATE PROCEDURE dbo.CompanyProfiles_AddProfile
	@ID		VARCHAR(50),
	@Name	NVARCHAR(256),
	@Body	NVARCHAR(MAX),
	@Primary	BIT
AS
BEGIN
	IF(@Primary = 1)
		UPDATE CompanyProfiles SET [Primary] = 0
		
	UPDATE CompanyProfiles SET Name = @Name, [Body] = @Body, [Primary] = @Primary WHERE ID = @ID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyProfiles_GetProfile') AND type in (N'P')))
	DROP PROCEDURE dbo.CompanyProfiles_GetProfile
GO
CREATE PROCEDURE dbo.CompanyProfiles_GetProfile
	@ID	VARCHAR(50),
	@Primary BIT
AS
BEGIN
	SELECT ID, Name, Body, [Primary] 
	FROM CompanyProfiles 
	WHERE (@Primary = 0 OR [Primary] = 1) AND
		(@Primary = 1 OR ID = @ID)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetFooter') AND type in (N'P')))
	DROP PROCEDURE dbo.GetFooter
GO
CREATE PROCEDURE dbo.GetFooter
AS
BEGIN
	SELECT [Content] FROM Footer
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.UpdateFooter') AND type in (N'P')))
	DROP PROCEDURE dbo.UpdateFooter
GO
CREATE PROCEDURE dbo.UpdateFooter
	@Content	NVARCHAR(4000)
AS
BEGIN
	DECLARE @Count INT
	SELECT @Count = COUNT(ID) FROM Footer
	
	IF(@Count = 0) BEGIN
		INSERT INTO Footer([Content])
		VALUES (N'')
	END
	
	UPDATE Footer SET [Content] = @Content
END
GO